package com.javaweb.dao.impl;

import com.javaweb.aop.NPECatch;
import com.javaweb.dao.RoleDao;
import com.javaweb.model.Permission;
import com.javaweb.model.Role;
import com.javaweb.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/9/24 22:44
 */

/**
 * todo:完成如下所有方法
 */
@Slf4j
@Repository
public class RoleDaoImpl implements RoleDao {

    @Autowired
    private Connection conn ;
    @Override
    @NPECatch
    public Integer insert(Role role) {
        String sql = "INSERT INTO tb_role (name) VALUES" +
                "(?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, role.getName()) ;


            return ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert  error: " + e.getMessage()) ;
        }
        return 0 ;

    }

    @Override
    public Integer insertRolePerm(Role role, Permission perm) {
        String sql = "INSERT INTO tb_role_per (role_id, per_id) VALUES" +
                "(?,?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1, role.getRoleId());
            ps.setInt(2,perm.getPerId());


            return ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insertRolePerm  error: " + e.getMessage()) ;
        }
        return 0 ;
    }

    @Override
    public List<Role> selectRolesByUserName(String username) {
        String sql = "SELECT r.* FROM tb_role r " +
                " JOIN tb_user_role ur ON r.role_id = ur.role_id" +
                " JOIN tb_user u ON u.user_id = ur.user_id" +
                "  WHERE u.name = ?" ;
        List<Role> roleList = new ArrayList<>() ;
        try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role()
                        .setRoleId(rs.getInt(1))
                        .setName(rs.getString(2))
                        ;
                roleList.add(role);
            }
            return roleList;
        } catch (SQLException e) {
            log.error("execute selectRolesByUserName error: " + e.getMessage());
        }

        return roleList;
    }

    @Override
    public List<Role> selectRolesByUserId(int userId) {
        String sql = "SELECT r.* FROM tb_role r " +
                " JOIN tb_user_role ur ON r.role_id = ur.role_id" +
                "  WHERE ur.user_id = ?" ;
        List<Role> roleList = new ArrayList<>() ;
        try (PreparedStatement ps = this.conn.prepareStatement(sql) ;) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Role role = new Role()
                        .setRoleId(rs.getInt(1))
                        .setName(rs.getString(2));
                roleList.add(role);
            }
            return roleList;
        } catch (SQLException e) {
            log.error("execute selectRolesByUserId error: " + e.getMessage());
        }

        return roleList;
    }

    @Override
    public void deleteByRoleId(int roleId)  {
        String sql = "delete from tb_role where role_id =?" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1, roleId); ;


            ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute deleteByRoleId error: " + e.getMessage()) ;
        }


    }

    @Override
    public void deleteUserRole(int userId, int roleId)  {
        String sql = "delete from tb_user_role where user_id=? and role_id =?" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1,userId);
            ps.setInt(2,roleId);


            ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute deleteUserRole error: " + e.getMessage()) ;
        }


    }
}
